package cs.utils;

import cs.entity.UserInfo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author qiuyihong
 * @description TODO
 * @date 2021/6/18 15:56
 */
public class DBUtils {


    public static List<UserInfo> query(String search) {
        Connection db = JDBCUtils.getDb();
        String sql = "select * from wgaes_user_info";

        if (search != null && !("null".equals(search)) && !"".equals(search)) {
            sql += " where department = '" + search + "'";
        }
        System.out.println(sql);
        Statement statement = null;
        ResultSet resultSet = null;
        List<UserInfo> list = new ArrayList<>();
        try {
            statement = db.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(resultSet.getInt(1));
                userInfo.setName(resultSet.getString(2));
                userInfo.setSex(resultSet.getString(3));
                userInfo.setAge(resultSet.getInt(4));
                userInfo.setUnit(resultSet.getString(5));
                userInfo.setProfessional(resultSet.getString(6));
                userInfo.setBasicSalary(resultSet.getInt(7));
                userInfo.setWelfare(resultSet.getInt(8));
                userInfo.setReward(resultSet.getInt(9));
                userInfo.setUnemploymentInsurance(resultSet.getInt(10));
                userInfo.setHousingProvidentFund(resultSet.getInt(11));
                userInfo.setDepartment(resultSet.getString(12));
                list.add(userInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.clear(statement, db, resultSet);
        }

        for (int i = 0; i < list.size(); i++) {
            UserInfo userInfo = list.get(i);
            int amount = userInfo.getBasicSalary() + userInfo.getWelfare() + userInfo.getReward();
            amount -= (userInfo.getUnemploymentInsurance() + userInfo.getHousingProvidentFund());
            userInfo.setAmount(amount);
        }

        return list;
    }


    public static int addUser(String name, String sex, String age, String unit, String professional, String basicSalary, String welfare, String reward, String unemploymentInsurance, String housingProvidentFund, String department) {

        Connection db = JDBCUtils.getDb();
        String sql = "insert wgaes_user_info values (null,'" + name + "','" + sex + "'," + age + ",'" + unit + "','" + professional + "'," + basicSalary + "," + welfare + "," + reward + "," + unemploymentInsurance + "," + housingProvidentFund + ",'" + department + "')";
        System.out.println(sql);
        Statement statement = null;
        try {
            statement = db.createStatement();
            return statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.clear(statement, db, null);
        }
        return 0;
    }

    public static int deleteUser(String id) {

        Connection db = JDBCUtils.getDb();
        String sql = "delete from wgaes_user_info where id = " + id;
        System.out.println(sql);
        Statement statement = null;
        try {
            statement = db.createStatement();
            return statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.clear(statement, db, null);
        }
        return 0;
    }


    public static List<Integer> queryCount() {
        List<Integer> list = new ArrayList<>();
        list.add(query("经理室").size());

        list.add(query("财务科").size());
        list.add(query("技术科").size());
        list.add(query("销售科").size());
        return list;
    }

}
